﻿			 -----------				BÁO CÁO KHÁCH HÀNG ACTIVE: DATABASE : ACTIVE_CUSTOMER_1			

	DECLARE @TDATE1 VARCHAR(8) SET @TDATE1 ='20160401'		 -- GIẢI NGÂN
	DECLARE @TDATE2 VARCHAR(8) SET @TDATE2 ='20160701'		 -- GIẢI NGÂN
	DECLARE @TDATE3 VARCHAR(8) SET @TDATE3 ='20160731'		 -- CHỐT BẢNG CUỐI THÁNG
	DECLARE @TDATE4 VARCHAR(8) SET @TDATE4 ='2016-02'		 -- BẢO LÃNH THÁNG
	DECLARE @TDATE5 VARCHAR(8) SET @TDATE5 ='2016-03'		 -- BẢO LÃNH THÁNG
	DECLARE @TDATE6 VARCHAR(8) SET @TDATE6 ='2016-04'		 -- BẢO LÃNH THÁNG
	DECLARE @TDATE7 VARCHAR(8) SET @TDATE7 ='2016-05'		 -- BẢO LÃNH THÁNG
	DECLARE @TDATE8 VARCHAR(8) SET @TDATE8 ='2016-06'		 -- BẢO LÃNH THÁNG
	DECLARE @TDATE9 VARCHAR(8) SET @TDATE9 ='2016-07'		 -- BẢO LÃNH THÁNG		-- CHÚ Ý THÁNG NÀY
	DECLARE @TDATE10 VARCHAR(20) SET @TDATE10 ='FEBRUARY-2016'	 -- LC THÁNG
	DECLARE @TDATE11 VARCHAR(20) SET @TDATE11 ='MARCH-2016'	 -- LC THÁNG
	DECLARE @TDATE12 VARCHAR(20) SET @TDATE12 ='APRIL-2016'	 -- LC THÁNG
	DECLARE @TDATE13 VARCHAR(20) SET @TDATE13 ='MAY-2016'		 -- LC THÁNG
	DECLARE @TDATE14 VARCHAR(20) SET @TDATE14 ='JUNE-2016'		 -- LC THÁNG
	DECLARE @TDATE15 VARCHAR(20) SET @TDATE15 ='JULY-2016'		 -- LC THÁNG		-- CHÚ Ý THÁNG NÀY
	DECLARE @TDATE16 VARCHAR(20) SET @TDATE16 ='201607'			 -- BALANCE_ENDMONTH
	DECLARE @TBALE1 VARCHAR(200) SET @TBALE1 = '[VUDT1].AD_HOC.DBO.SME_TRANSACTION_M02_2016'
	DECLARE @TBALE2 VARCHAR(200) SET @TBALE2 = '[VUDT1].AD_HOC.DBO.SME_TRANSACTION_M03_2016'
	DECLARE @TBALE3 VARCHAR(200) SET @TBALE3 = '[VUDT1].AD_HOC.DBO.SME_TRANSACTION_M04_2016'
	DECLARE @TBALE4 VARCHAR(200) SET @TBALE4 = '[VUDT1].AD_HOC.DBO.SME_TRANSACTION_M05_2016'
	DECLARE @TBALE5 VARCHAR(200) SET @TBALE5 = '[VUDT1].AD_HOC.DBO.SME_TRANSACTION_M06_2016'
	DECLARE @TBALE6 VARCHAR(200) SET @TBALE6 = '[VUDT1].AD_HOC.DBO.SME_TRANSACTION_M07_2016'

------------------ ACTIVE CUSTOMER
   EXEC ('IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = ''ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+''' AND TYPE = ''U'')
       DROP TABLE ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+'')
EXEC('
SELECT A.RECID AS CIF,A.CUS_NAME,A.LEGAL_ID,A.DAO,A.COMPANY_BOOK AS BRANCH_CODE,B.BRANCH_NAME_SME AS BRANCH_NAME,
REGION =CASE	WHEN B.ZONE_ID_SME = ''VUNG 01'' THEN ''REGION 1''
				WHEN B.ZONE_ID_SME = ''VUNG 02'' THEN ''REGION 2''
				WHEN B.ZONE_ID_SME = ''VUNG 03'' THEN ''REGION 3''
				WHEN B.ZONE_ID_SME = ''VUNG 04'' THEN ''REGION 4''
				WHEN B.ZONE_ID_SME = ''VUNG 05'' THEN ''REGION 5''
				WHEN B.ZONE_ID_SME = ''VUNG 06'' THEN ''REGION 6''
				WHEN B.ZONE_ID_SME = ''VUNG 07'' THEN ''REGION 7''
				ELSE ''UNALLOCATED''
				END
			 
INTO ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+'
FROM SERVER74.BICDATA.DBO.CUSTOMER A
LEFT JOIN SERVER74.BICDATA.DBO.BRANCH_CODE B
ON A.COMPANY_BOOK=B.BRANCH_ID
WHERE CONVERT(DATE,CUS_OPEN_DATE) <= '''+@TDATE3+'''
AND SEGMENT = ''SMES''
')

PRINT('1')

EXEC('
ALTER TABLE ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+'
ADD PRO_CASA VARCHAR(4),PRO_TD VARCHAR(4),PRO_LOAN VARCHAR(4)
,BAL_TD VARCHAR(4),BAL_TD_OTHER VARCHAR(4),BAL_CASA VARCHAR(4),BAL_LOAN VARCHAR(4) 
')

-- UPDATE ACTIVE TRANSACTION
EXEC('
UPDATE ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+'
SET PRO_CASA = ''1''
FROM ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+' A,
(SELECT CUSTOMER_ID AS CIF FROM 
(
--SELECT DISTINCT CUSTOMER_ID FROM '+@TBALE1+'
--WHERE INITIATION IN (''CUSTOMER'',''AUTO'')
--UNION 
--SELECT DISTINCT CUSTOMER_ID FROM '+@TBALE2+'
--WHERE INITIATION IN (''CUSTOMER'',''AUTO'')
--UNION 
SELECT DISTINCT CUSTOMER_ID FROM '+@TBALE3+'
WHERE INITIATION IN (''CUSTOMER'',''AUTO'')
UNION 
SELECT DISTINCT CUSTOMER_ID FROM '+@TBALE4+'
WHERE INITIATION IN (''CUSTOMER'',''AUTO'')
UNION 
SELECT DISTINCT CUSTOMER_ID FROM '+@TBALE5+'
WHERE INITIATION IN (''CUSTOMER'',''AUTO'')
UNION 
SELECT DISTINCT CUSTOMER_ID FROM '+@TBALE6+'
WHERE INITIATION IN (''CUSTOMER'',''AUTO'')

)X
)B
WHERE A.CIF =B.CIF
')
PRINT('2')
---- UPDATE PRODUCT TERM DEPOSIT
 EXEC('
UPDATE ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+'
SET PRO_TD =''1''
FROM ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+' A,
(
SELECT DISTINCT CIF FROM SERVER74.BICDATA.DBO.DPTB_MASTER
WHERE PRODUCT_NAME = ''2. TERM DEPOSIT''
AND SEGMENT = ''SMES''
AND OPENNING_DATE BETWEEN '''+@TDATE1+''' AND '''+@TDATE3+'''
 ) B
 WHERE A.CIF =B.CIF
')
 --- UPDATE PRODUCT LOAN
 PRINT('4')
 EXEC('
 UPDATE ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+'
 SET PRO_LOAN =''1''
 FROM ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+' A,
 (
 SELECT * FROM (
 SELECT DISTINCT CIF FROM SERVER74.BICDATA.DBO.LNTB_DISBURSEMENT 
 WHERE SEGMENT = ''SMES''
 AND DIST_DATE BETWEEN '''+@TDATE1+''' AND '''+@TDATE3+'''
 AND CIF IS NOT NULL
 UNION 
 SELECT DISTINCT CIF FROM SERVER12.ANHCP.DBO.[DOANH_SO_TTR_LC_DP]
 WHERE [MONTH] IN (
 --'''+@TDATE10+''','''+@TDATE11+''',
 '''+@TDATE12+''','''+@TDATE13+''','''+@TDATE14+''','''+@TDATE15+''')
 AND CIF IS NOT NULL
 UNION
 SELECT DISTINCT CUST_ID AS CIF FROM SERVER12.TRANGLT9.DBO.DOANH_SO_BAO_LANH 
 WHERE [MONTH] IN (
 --'''+@TDATE4+''','''+@TDATE5+''',
 '''+@TDATE6+''','''+@TDATE7+''','''+@TDATE8+''','''+@TDATE9+''')
 AND CUST_ID IS NOT NULL
 ) B
    )B
 WHERE A.CIF =B.CIF
 ')
PRINT('3')
 EXEC(' 
 UPDATE ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+'
 SET BRANCH_NAME = ''UNALLOCATED''
 WHERE REGION =''UNALLOCATED''
 ')

 
  EXEC('
 SELECT CIF,COUNT(ACCTNO) AS ACCTNO,COUNT(WITHDRAW_DATE) AS [WITHDRAW_DATE]
INTO ##A
 FROM SERVER74.BICDATA.DBO.DPTB_MASTER
WHERE SEGMENT <>''KHCN'' AND
CIF IN (SELECT CIF FROM ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+')
GROUP BY CIF	  ')

EXEC('
UPDATE 	ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+'
SET PRO_CASA = NULL , PRO_TD= NULL
FROM ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+' A,( SELECT * FROM ##A WHERE  ACCTNO = WITHDRAW_DATE) B
WHERE A.CIF =B.CIF
	  ')


EXEC('
ALTER TABLE ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+'
ADD TT_KH VARCHAR(4) ')

EXEC('
 UPDATE ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+'
SET TT_KH = ''0''
WHERE PRO_CASA IS NULL AND PRO_LOAN IS NULL AND PRO_TD IS NULL
')

EXEC('
UPDATE ACTIVE_CUSTOMER_4_MONTH_'+@TDATE3+'
SET TT_KH = ''1''
WHERE TT_KH IS NULL
')

 --select * from ACTIVE_CUSTOMER_4_MONTH_20160731 where tt_kh = 0
 drop table TBL_CM_ALERT_INACTIVE

 --DROP TABLE ##A
 SELECT C.*, d.BRANCH_CODE_SME, BRANCH_NAME_SME, d.REGION
 INTO TBL_CM_ALERT_INACTIVE
 FROM VUDT1.ACTIVE_CUSTOMER_1.DBO.ACTIVE_CUSTOMER_20160731 A, ACTIVE_CUSTOMER_4_MONTH_20160731 B, TBL_CUSTOMER C, TBL_BRANCH d
 WHERE A.CIF = B.CIF AND A.TT_KH=1 
 AND B.TT_KH=0 AND C.CIF=A.CIF and c.BRANCH_ID=d.BRANCH_ID

 SELECT * FROM TBL_BRANCH WHERE BRANCH_ID= 'VN0010231'
 SELECT * FROM TBL_CM_ALERT_INACTIVE WHERE CIF ='1216230'

 SELECT * FROM ANHCP.CEO_PERFORMANCE.[dbo].[branchoff] WHERE BRANCH_CODE='VN0010231'

 
 delete from [DBCustomer].dbo.TBL_CM_ALERT_INACTIVE
insert into [DBCustomer].dbo.TBL_CM_ALERT_INACTIVE
select * from TBL_CM_ALERT_INACTIVE

 EXEC('IF EXISTS
      (SELECT *
         FROM [SERVER12].[SMECustomer360].DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''TBL_CM_ALERT_INACTIVE'')
        delete from [SERVER12].[SMECustomer360].dbo.TBL_CM_ALERT_INACTIVE')
 
insert [SERVER12].[SMECustomer360].dbo.TBL_CM_ALERT_INACTIVE
select * from TBL_CM_ALERT_INACTIVE